<?php
/**
 * compound
 * 化合物
 * @author Administrator
 * @version
 */
require_once ROOT_PATH . '/library/Common/Classes/Upload.php';
require_once ROOT_PATH . '/library/Common/Classes/Babel.php';
require_once ROOT_PATH . '/library/Common/Classes/DBCache.php';
class Model_Tables_Compound extends Zend_Db_Table_Abstract {
    /**
     * The default table name
     */
    protected $_name = 'compound';
    
    protected $basepath='/data/www/smartchem/public/upload/file/';
    private function createSVG($id,$smile,$synthesisroute){
    	$babel = new Babel();
    	$babel->mol2gif($id, $smile);
    	$babel->rxn2gif($id,$synthesisroute);
    }
    /**
     * add
     * 增加化合物
     * @param $params 字段信息
     */
    public function add( $params,$userInfo )
    {
    	$upload = new Upload();
    	$modelCompoundreference = new Model_Tables_Compoundreference();
    	$modelCompoundspectrum = new Model_Tables_Compoundspectrum();
    	$modelCompoundSave = new Model_Tables_Compoundsave();
    	$docfile = time();    	
    	file_put_contents($this->basepath.$docfile, $params['synthesisdoc']);
    	$babel = new Babel();
    	$props = $babel->molecule_to_prop($params['mol']);
        $data = array(
		'casno' => $params['casno'],
		'compoundno' => $params['compoundno'],
		'compoundname' => $params['compoundname'],
		'compoundnameen' => $params['compoundnameen'],
        'mol' => $props["mol"],
        'smile' => $props["canonical_SMILES"],
        'inchi' =>$props["InChI"],
        'formula' => $props["formula"],
        'molecularweight' => $props["mol_weight"],
        'logp' => $props["logP"],
        'psa' => $props["PSA"],
        'mr' => $props["MR"],
        //'mol' =>  $this->GetMol($params['mol']),
        //'smile' =>  $this->GetSmile($params['mol']),
        //'inchi' =>  $this->GetInchi($params['mol']),
        //'formula' => $this->GetFormula($params['mol']),//$params['formula'],
		//'molecularweight' => $this->GetWeight($params['mol']),//$params['molecularweight'],
        'product' => $params['product'],
		'patent' => $params['patent'],
		'appearance' => $params['appearance'],
		'meltingpoint' => $params['meltingpoint'],
		'boilingpoint' => $params['boilingpoint'],
		'solubility' => $params['solubility'],
		'stability' => $params['stability'],
		'projectid' => $params['projectid'],
		'synthesisroute' =>   $this->GetMol($params['synthesisroute']),
        'noteid' => $params['noteid'],
        'notepage' => $params['notepage'],
        'optical' => $params['optical'],
        'chirality' => $params['chirality'],
        //'logp' => $params['logp'],
		'synthesisdoc' => $docfile,
        'username' => $userInfo['realname'],
        'userid' => $userInfo['userid'],
        'salt' => $params['salt'],
        'solvent' => $params['solvent'],
        'addtime' => date("Y-m-d H:m",time()),
        'modtime' => date("Y-m-d H:m",time()),
        );
        $ids = $this->getIdListfine($data["smile"]);
        if(count($ids)>0){
        	return array($ids[0],1);
        }
        $id = $this->insert($data);//compound 数据
        //参考文献
//         $referencefiles = $upload->uploadMultiFile("reference","/tmp",false,false);
//         //var_dump($files);
//         $referencesnames = $params['referencesname'];
//         foreach($referencefiles as $k=>$v){
//         	$params["compoundid"]=$id;
//         	$params["referencesurl"]=$v["to"];
//         	//$params["referencesname"]=$referencesnames[$k];
//         	$params["referencesname"]=$v["from"];
//         	$params["orderid"]=$k;
//         	$modelCompoundreference->add($params);
//         }
        //谱图
//         $spectrumfiles = $upload->uploadMultiFile("spectrum","/tmp",false,false);
//         $spectrumtypeids = $params['spectrumtypeid'];
//         $spectrumnames = $params['spectrumname'];
//         $spectrumdess = $params['spectrumdes'];
//         foreach($spectrumfiles as $k=>$v){
//         	$params["compoundid"]=$id;
//         	$params["spectrumtypeid"]=$spectrumtypeids[$k];
//         	//$params["spectrumname"]=$spectrumnames[$k];
//         	$params["spectrumname"]=$v["from"];
//         	$params["spectrumdes"]=$spectrumdess[$k];
//         	$params["spectrumurl"]=$v["to"];
//         	$params["orderid"]=$k;
//         	$modelCompoundspectrum->add($params);
//         }
        //样品
//         $batchs = $params['batch'];
//         $puritys = $params['purity'];
//         $containers = $params['container'];
//         $locations = $params['location'];
//         $producedates = $params['producedate'];
//         foreach($batchs as $k=>$v){
//         	$params["compoundid"]=$id;
//         	$params["batch"]=$v;
//         	$params["purity"]=$puritys[$k];
//         	$params["container"]=$containers[$k];
//         	$params["location"]=$locations[$k];
//         	$params["producedate"]=$producedates[$k];
//         	$params["orderid"]=$k;
//         	$modelCompoundSave->add($params);
//         }
        //$this->UpdateNewSerialized();
        $this->createSVG($id, $params['mol'],$params['synthesisroute']);
        return array($id,-1);
    }
    /**
     * modify
     * 编辑化合物
     * @param $params 字段信息
     * @param $id 主键值
     */
    public function modify( $params, $id, $docfile )
    {
    	$upload = new Upload();
    	$modelCompoundreference = new Model_Tables_Compoundreference();
    	$modelCompoundspectrum = new Model_Tables_Compoundspectrum();
    	$modelCompoundSave = new Model_Tables_Compoundsave();
    	if(!$docfile)
    		$docfile = time();    	
    	file_put_contents($this->basepath.$docfile, $params['synthesisdoc']);
    	$babel = new Babel();
    	$props = $babel->molecule_to_prop($params['mol']);
    	//print_r($props);exit;
        $data = array(
		'casno' => $params['casno'],
		'compoundno' => $params['compoundno'],
		'compoundname' => $params['compoundname'],
		'compoundnameen' => $params['compoundnameen'],
        'compoundnameother' => $params['compoundnameother'],
        'mol' => $props["mol"],
        'smile' => $props["canonical_SMILES"],
        'inchi' =>$props["InChI"],
        'formula' => $props["formula"],
        'molecularweight' => $props["mol_weight"],
        'logp' => $props["logP"],
        'psa' => $props["PSA"],
        'mr' => $props["MR"],
        //'mol' =>  $this->GetMol($params['mol']),
        //'smile' => $this->GetSmile($params['mol']),//$params['smile'],
        //'inchi' =>  $this->GetInchi($params['mol']),
		//'formula' => $this->GetFormula($params['mol']),//$params['formula'],
		//'molecularweight' => $this->GetWeight($params['mol']),//$params['molecularweight'],
		'product' => $params['product'],
		'patent' => $params['patent'],
		'appearance' => $params['appearance'],
		'meltingpoint' => $params['meltingpoint'],
		'boilingpoint' => $params['boilingpoint'],
		'solubility' => $params['solubility'],
		'stability' => $params['stability'],
		'projectid' => $params['projectid'],
		'synthesisroute' => $this->GetMol($params['synthesisroute']),
        'noteid' => $params['noteid'],
        'notepage' => $params['notepage'],
        'optical' => $params['optical'],
        'chirality' => $params['chirality'],
        //'logp' => $params['logp'],
        'synthesisdoc' => $docfile,
        'salt' => $params['salt'],
        'solvent' => $params['solvent'],
        'status' => $params['status'],
        'modtime' => date("Y-m-d H:m",time()),
        );
        $where = 'id = '.$id ;
        $synthesisfiles = $upload->uploadMultiFile("synthesisfile","/tmp",false,false);
        if(count($synthesisfiles)>0 && $synthesisfiles[0]["to"]!=""){
        	$data["synthesisfileurl"] = $synthesisfiles[0]["to"];
        	$data["synthesisfilename"] = $synthesisfiles[0]["from"];
        }
        $this->update($data , $where );
        //参考文献
//         $referencefiles = $upload->uploadMultiFile("reference","/tmp",false,false);
//         $referencesids = $params['referencesid'];
//         $referencesnames = $params['referencesname'];
//         echo "<pre>a";
//         print_r($referencesnames);
//        exit;
//         foreach($referencesids as $k=>$v){
//         	$params["compoundid"]=$id;
//         	if(isset($referencefiles[$k])){
//         		$params["referencesurl"]=$referencefiles[$k]["to"];
//         		$params["referencesname"]=$referencefiles[$k]["from"];
//         	}
//         	$params["orderid"]=$k;
//         	if($v>0){
//         		if($params['referencesname'])
//         			$modelCompoundreference->modify($params, $v["to"]);
//         		else 
//         			$modelCompoundreference->del($v["to"]);
//         	}else{
//         		if($params['referencesname']&&$params['referencesurl'])
//         			$modelCompoundreference->add($params);
//         	}
//         }
        //谱图
//         $spectrumfiles = $upload->uploadMultiFile("spectrum","/tmp",false,false);
//         $spectrumtypeids = $params['spectrumtypeid'];
//         $spectrumnames = $params['spectrumname'];
//         $spectrumids = $params['spectrumid'];
//         $spectrumdess = $params['spectrumdes'];
//         foreach($spectrumids as $k=>$v){
//         	$params["compoundid"]=$id;
//         	$params["spectrumtypeid"]=$spectrumtypeids[$k];
//         	//$params["spectrumname"]=$spectrumnames[$k];
//         	$params["spectrumname"]=$spectrumfiles[$k]["from"];
//         	$params["spectrumdes"]=$spectrumdess[$k];
//         	if(isset($spectrumfiles[$k]))
//         	$params["spectrumurl"]=$spectrumfiles[$k]["to"];
//         	$params["orderid"]=$k;
//         	if($v>0){
//         		if($params["spectrumname"])
//         			$modelCompoundspectrum->modify($params, $v["to"]);
//         		else 
//         			$modelCompoundspectrum->del($v["to"]);
//         	}else{
//         		if($params["spectrumname"])
//         			$modelCompoundspectrum->add($params);
//         	}
//         }
        //样品
//         $batchs = $params['batch'];
//         $puritys = $params['purity'];
//         $containers = $params['container'];
//         $locations = $params['location'];
//         $producedates = $params['producedate'];
//         $saveids = $params['saveid'];
//         foreach($saveids as $k=>$v){
//         	$params["compoundid"]=$id;
//         	$params["batch"]=$batchs[$k];
//         	$params["purity"]=$puritys[$k];
//         	$params["container"]=$containers[$k];
//         	$params["location"]=$locations[$k];
//         	$params["producedate"]=$producedates[$k];
//         	$params["orderid"]=$k;
//         	if($v>0){
//         		if($params["batch"])
//         			$modelCompoundSave->modify($params, $v);
//         		else
//         			$modelCompoundSave->del($v);
//         	}else{
//         		if($params["batch"])
//         			$modelCompoundSave->add($params);
//         	}
//         	//$modelCompoundSave->add($params);
//         }
        //var_dump($referencefiles);exit;
        $this->createSVG($id, $params['mol'],$params['synthesisroute']);
        //$this->UpdateSerialized($id);
        return true;
    }
    /**
     * modifyAjax
     * 无刷新编辑化合物
     * @param $id 主键值
     * @param $field 字段
     * @param $value 值
     */
    public function modifyAjax( $id, $field, $value )
    {
    	$db = $this->getAdapter();
    	$data = array($field => $value);
    	$where = $db->quoteInto("id=?", $id);
        return $this->update($data , $where );
    }
    /**
     * modifyAjaxRevers
     * 无刷新字段反转化合物
     * @param $field 需要反转的字段
     * @param $id 主键值
     */
    public function modifyAjaxRevers( $field ,$pri)
    {
    	$db = $this->getAdapter();
    	$sql = "update compound set  $field =not  $field  where id=' $pri'";
		return $db->query($sql);
    }
    /**
     * del
     * 根据条件删除化合物
     * @param $id 主键值
     */
    public function del( $id )
    {
     	$where = 'id = '.$id;
        return $this->delete($where);
    }
    /**
     * getInfo
     * 获取化合物信息
     * @param $id 主键值
     * @return 结果数组
     */
    public function getInfo($id)
    {
        $row = $this->fetchRow('id = ' . $id);
        if (!$row) {
        	return null;
        }
        else
        {
        	$result = $row->toArray();
        	$result["docfile"]=$result["synthesisdoc"];
        	$result["synthesisdoc"]=file_get_contents($this->basepath.$result["synthesisdoc"]);
        	return $result;
        }
    }
    public function getInfoBycompoundno($compoundno){
    	$sql = "select id,projectid from compound where compoundno=:compoundno";
    	$result = $this->getAdapter()->fetchRow($sql,array("compoundno"=>$compoundno));
   		return $result;
    }
    public function GetMol($mol){
    	//$db = $this->getAdapter();
    	//$sql = "select molecule_to_smiles(smiles_to_molecule('$smile'))";
    	//return $db->fetchOne($sql);
    	$mol = str_replace("\r\n", "{#n#}", $mol);
    	$mol = str_replace("Untitled Document", "", $mol);
    	$mol = str_replace("ChemDraw", "SmartChem", $mol);
    	return $mol;
    }
    public function GetSmile($mol){
    	$babel = new Babel();
    	return $babel->molecule_to_smiles($mol);
    	/*$mol = str_replace("{#n#}","\r\n",  $mol);
    	$db = $this->getAdapter();
    	$sql = "select molecule_to_smiles('$mol')";
    	return $db->fetchOne($sql);*/
    }
    public function GetInchi($mol){
    	$babel = new Babel();
    	return $babel->molecule_to_inchi($mol);
    	/*$mol = str_replace("{#n#}","\r\n",  $mol);
    	 $db = $this->getAdapter();
    	$sql = "select molecule_to_smiles('$mol')";
    	return $db->fetchOne($sql);*/
    }
    public function GetFormula($mol){
    	$mol = str_replace("{#n#}","\r\n",  $mol);
    	$db = $this->getAdapter();
    	$sql = "select molformula('$mol')";
    	return $db->fetchOne($sql);
    }
	public function GetWeight($mol){
		$mol = str_replace("{#n#}","\r\n",  $mol);
    	$db = $this->getAdapter();
    	$sql = "select molweight('$mol')";
    	return $db->fetchOne($sql);
	}
	public function UpdateSerialized($id){
		$db = $this->getAdapter();
    	$sql = "update compound set compoundserialized = MOLECULE_TO_SERIALIZEDOBMOL(SMILES_TO_MOLECULE(smile)) where id='$id'";
    	return $db->query($sql);
	}
	public function UpdateNewSerialized(){
		$db = $this->getAdapter();
    	$sql = "update compound set compoundserialized = MOLECULE_TO_SERIALIZEDOBMOL(SMILES_TO_MOLECULE(smile)) order by id desc limit 1";
    	return $db->query($sql);
	}
	public function getCountAll(){
		$select = $this->select()->from($this->_name,'count(*) as num');
		$result = $this->fetchRow($select)->num;
		return $result;
	}
    /**
     * getCount
     * 获取结构式数量
     * @param int  搜索条件
     * @return 结果数
     */
    public function getCount($r = array())
    { 
    	if(count($r)==0){
    		return $this->getCountAll();
    	}
    	$ids = $this->getIDList($r);
    	$result = count($ids);
        return $result;
    }
    /**
     * 精确搜索
     * */
    public function getIdListfine($smile){
    	$babel = new Babel();
    	$inchi = $babel->smile_to_inchi($smile);
    	$inchi = trim($inchi);
    	$db = $this->getAdapter();
    	$sql = "select id from compound where inchi COLLATE utf8_bin =:inchi order by id desc";
    	$result0 = $db->fetchAll($sql,array("inchi"=>$inchi));//非结构检索
    	foreach($result0 as $k=>$v){
    		$result[] = $v["id"];
    	}
    	return $result;
    }
    public function getIDList($r){
    	$dbcache = new DBCache();
    	$cache = $dbcache->getCache("searchid");
    	$key = md5(serialize($r));
    	if(!$result = $cache->load($key)){
	    	$likefilter = array("casno","compoundno", "compoundname", "compoundnameen", "formula");
	    	$lfilter =  array("molecularweightl");//下限
	    	$hfilter = array("molecularweighth");//上限
	    	$infilter = array("projectid","id");
	    	$eqfilter = array("status");
	    	$model_Tables_Project = new Model_Tables_Project();
	    	$plist = $model_Tables_Project->getList(1,100,$r);
	    	//$r["projectid"] = "-1";
	    	if($r["projectid"] == "-1"){
	    		foreach($plist as $item){
	    			$r["projectid"] .= ",".$item["id"];
	    		}	
	    	}
	    	$page = (1>$page) ? 1 : $page;
	    	$offset = ($page-1)*$numPerPage;
	    	$orderby = array('id desc');
	    	$db = $this->getAdapter();
	    	$where = " 1=1 ";
	    	foreach($r as $k=>$v){
	    		if($k=="any"){
	    			if($v){
	    				$where = $db->quoteInto(" (casno like ? ","%$v%");
	    				$where .= $db->quoteInto(" or compoundno like ? ","%$v%");
	    				$where .= $db->quoteInto(" or compoundname like ? ","%$v%");
	    				$where .= $db->quoteInto(" or compoundnameen like ? ","%$v%");
	    				$where .= $db->quoteInto(" or formula like ?) ","%$v%");
	    				if($r["projectid"] != "-1")
	    					$where .= " and projectid in(".$r["projectid"].")";
	    				if($r["status"] && $r["status"] != "-1")
	    					$where .= $db->quoteInto(" and status = ? ",$r["status"]);
	    				//echo $where;//exit;
	    			}
	    		}else{
	    			if(in_array($k,$likefilter)){
	    				if($v)
	    					$where .= $db->quoteInto(" and $k like ? ","%$v%");
	    			}else if(in_array($k,$lfilter)){
	    				if($v)
	    					$where .= $db->quoteInto(" and molecularweight >= ? ",$v);
	    			}else if(in_array($k,$hfilter)){
	    				if($v)
	    					$where .= $db->quoteInto(" and molecularweight <= ? ",$v);
	    			}else if(in_array($k,$infilter)){
	    				if($v && $v!="-1"){
	    					$where .= " and $k in ($v) ";
	    				}
	    					
	    			}else if(in_array($k,$eqfilter)){
	    				if($v && $v!="-1"){
	    					$where .= $db->quoteInto(" and $k = ? ",$v);
	    				}
	    			}
	    		}
	    	}
	    	$sql = "select id from compound where $where order by id desc";
	    	//echo $sql;exit;
	    	$result0 = $db->fetchAll($sql);//非结构检索
	    	$result2 = array();
	    	$result3 = array();
	    	foreach($result0 as $k=>$v){
	    		$result2[] = $v["id"];
	    	}
	    	$result1 = $this->getIdListbySmi($r);
	    	if($result1 == "all"){
	    		$result = $result2;
	    	}else{
	    		foreach($result1 as $k=>$v){
	    			$result3[] = trim($v);
	    		}
	    		$result = array_intersect($result3,$result2);
	    	}
	    	$cache->save($result,$key);
    	}
    	//print_r($result);exit;
    	return $result;
    }
    private function getIdListbySmi($r){
    	$stype = $r["stype"];//检索类型
    	if(!$stype){
    		return "all";
    	}
		
    	$smi = $this->GetSmile($r["mol"]);//smile
    	if(!$smi){
    		return "all";
    	}
    	$x = $r["x"];//相似度
    	if($stype == "3"){
    		$result = $this->getIdListfine($smi);
    	}else
    	{
    	$babel = new Babel();
    	$result = $babel->getIdListbySmi("/data/yuecl/smartchem.fs", $smi, $stype, $x);
    	}
    	return $result;
    }
    //无条件搜索
    private function getListAll($page, $numPerPage = 10){
    	$page = (1>$page) ? 1 : $page;
    	$offset = ($page-1)*$numPerPage;
    	$orderby = array('id desc');
    	$db = $this->getAdapter();
    	$result0 = $this->fetchAll(null,$orderby, $numPerPage, $offset )->toArray();
    	foreach($result0 as $item){
    		$offset ++;
    		$item["sid"] = $offset;
    		$result[] = $item;
    	}
    	return $result;
    }
    /**
     * getList
     * 取结构式数据分页
     * @param int  页码
     * @param int  每页的行数 
     * @param  搜索条件
     * @return 行数组
     */
    public function getList($page, $numPerPage = 10,$r = array())
    {
    	if(count($r)==0){
    		return $this->getListAll($page,$numPerPage);
    	}
    	$ids = $this->getIDList($r);
    	$page = (1>$page) ? 1 : $page;
    	$offset = ($page-1)*$numPerPage;
    	$sliceids = array_slice($ids,$offset,$numPerPage);
    	$strids = implode(",",$sliceids);
    	if($strids)
    		$where = " where id in ($strids)";
    	else 
    		$where = " where id in (0)";
    	$sql = "select * from compound $where order by id desc";
    	//echo $sql ;exit;
    	$result0 = $this->getAdapter()->fetchAll($sql);//非结构检索
        foreach($result0 as $item){
        	$offset ++;
        	$item["sid"] = $offset; 
        	$result[] = $item;	
        }
        return $result;
    }
    public function checkno($compoundno,$id=-1){
    	$sql = "select count(*) from compound where compoundno = :compoundno and id <> :id";
    	$n = $this->getAdapter()->fetchOne($sql,array("compoundno"=>$compoundno,"id"=>$id));
    	return !$n;
    }
    public function getnewid(){
    	$sql = "select max(id)+1 as id from compound";
    	$id = $this->getAdapter()->fetchOne($sql);
    	$id += 100000000;
    	return substr($id,1);
    }
    public function updatestatus($compoundid,$status,$reason,$operator){
    	$checkdate = date("Y-m-d H:m",time());
    	$sqlcompound = "update compound set status = :status where id = :id";
    	$sqlreason = "insert into reasons (compoundid, status, operator, checkdate, reason) values (:compoundid, :status, :operator, :checkdate, :reason)";
    	$db = $this->getAdapter();
    	$db->beginTransaction();
    	try{
    		$db->query($sqlcompound,array("status"=>$status,"id"=>$compoundid));
    		$db->query($sqlreason,array("compoundid"=>$compoundid, "status"=>$status, "operator"=>$operator, "checkdate"=>$checkdate, "reason"=>$reason));
    		$db->commit();
    	}catch (Exception $e){
		    $db->rollBack();
		}
    }
}